HealthCareAnalysis_SQL(Python)

all_columns=df.columns
print(all_columns)

newdf=df.withColumnRenamed("Blood Type","Blood_Type").withColumnRenamed("Medical Condition","Medical_Condition").withColumnRenamed("Date of Admission","Date_Admited").withColumnRenamed("Insurance Provider","Insurance_Provider").withColumnRenamed("Billing Amount","Billing_Amount").withColumnRenamed("Room Number","Room_Number").withColumnRenamed("Admission Type","Admission_Type").withColumnRenamed("Discharge Date","Discharge_Date").withColumnRenamed("Test Results","Test_results")
display(newdf)

print(newdf.columns)

newdf.createOrReplaceTempView("Sample2")

#dataset
display(spark.sql("Select * from sample2"))

#total paients admited in differnet hospitals
display(spark.sql("select hospital,count(distinct(name)) as total_patient_count from sample2 group by hospital order by total_patient_count desc"))

#Gender based average age of developing a particular disease
display(spark.sql("select medical_condition,gender,round(avg(age),0) as avg_age from sample2 group by medical_condition,gender order by medical_condition,gender"))

#Different medical conditions
display(spark.sql("select distinct(Medical_condition) from sample2"))

#Total revenue generated by all the hospitals
display(spark.sql("select round(sum(Billing_Amount),3) as Total_Revenue from sample2"))

#Count of cases based on diseases
display(
    spark.sql("select Medical_condition,count(Medical_condition) as Toatl_Count from sample2 group by Medical_condition order by count(Medical_condition) desc "
    )
)

#Total revenue generated by hospitals
display(spark.sql("select Hospital,round(sum(Billing_Amount),2) from Sample2 group by Hospital order by sum(Billing_Amount) desc limit 5"))

#Total number of male and female patients
display(spark.sql("select gender, count(*) from sample2 group by gender"))

#Gender based count of diseases
display(spark.sql("select medical_condition,sum(case when gender='Female' then 1 else 0 end) as Female_count,sum(case when gender='Male' then 1 else 0 end) as Male_count from sample2 group by medical_condition"))

#Average days of treatment for diseases
display(spark.sql("Select medical_condition,round(avg(datediff(discharge_date,date_admited)),0)as avg_days_of_treatment from sample2 group by medical_condition"))

#Count of different types of admissions in different hospitals
display(spark.sql("select hospital, sum(case when admission_type='Emergency' then 1 else 0 end)as Emergency,sum(case when admission_type='Elective' then 1 else 0 end)as Elective,sum(case when admission_type='Urgent' then 1 else 0 end)as Urgent from sample2 group by hospital order by emergency desc"))

#Total insurance amount provided by each provider
display(spark.sql("select insurance_provider,round(sum(billing_amount),3) as total_amount from sample2 group by insurance_provider order by total_amount desc"))

#Insurance provided bases in diseases
display(spark.sql("select insurance_provider,medical_condition,round(sum(billing_amount),3) as total_amount,count(name) as total_patients_claimed from sample2 group by insurance_provider,medical_condition order by insurance_provider,total_amount desc"))

#Count of different cases based on diseases
display(spark.sql("select medical_condition, sum(case when test_results='Normal' then 1 else 0 end)as Normal,sum(case when test_results='Inconclusive' then 1 else 0 end)as Inconclusive,sum(case when test_results='Abnormal' then 1 else 0 end)as abnormal from sample2 group by medical_condition"))

#Top 3 doctors for each disease 
display(spark.sql("with cte as(select medical_condition,doctor,count(doctor) as cnt from sample2 group by medical_condition,doctor order by medical_condition asc, cnt desc),cte2 as(select *,row_number() over(partition by medical_condition order by cnt desc) as drn from cte) select medical_condition,doctor from cte2 where drn<=3"))